import dayjs from 'dayjs';
import jwt from 'jsonwebtoken';
import { jwtSecretMd5 } from '../config';
import crypto from '../util/crypto';
import path from 'path';
import fs from 'fs';
import nodexlsx from 'node-xlsx';
import {
  formatReqLog, selectQueryFunc,
  verifyAuthentication, mkdirs
} from '../util/';

const login = async (req: any, res: any) => {
  const data = req.body.data || req.body || {};
  const regex = /captchaCode=([^;]+)/;
  const cookie = req.headers.cookie?.match(regex)[1];
  if (!cookie || data.captcha !== crypto.decrypt(cookie)) {
    return res.send(formatReqLog(
      400, '验证码错误', null, { goal: req.originalUrl })
    );
  }
  if (data.user) {
    const isUser = await selectQueryFunc(`SELECT * FROM users WHERE [user]='${data.user}'`);
    if (isUser.data) {
      const isPass = await selectQueryFunc(`SELECT * FROM users WHERE [user]='${data.user}' AND pass='${crypto.decrypt(data.pass)}'`);
      if (isPass.data) {
        const token = jwt.sign({
          id: isUser.data.id
        }, jwtSecretMd5, {
          expiresIn: 60 * 60 * 1000 * 24 * 1 // 1天过期
        });
        isPass.data.pass = undefined;
        res.send(formatReqLog(200, '登录成功', isPass.data, { token, goal: req.originalUrl, user: isUser.data.user }));
        await mkdirs(path.join(__dirname, `../static/${isUser.data.user}/`));
        await mkdirs(path.join(__dirname, '../static/temporary/'));
        await mkdirs(path.join(__dirname, `../static/temporary/${isUser.data.user}/`));
      } else {
        res.send(formatReqLog(300, `${data.user}密码错误`, null, { goal: req.originalUrl, user: data.user }));
      }
    } else {
      res.send(formatReqLog(300, `不存在该${data.user}用户`, null, { goal: req.originalUrl, user: data.user }));
    }
  } else {
    res.send(formatReqLog(400, '登录失败', null, { goal: req.originalUrl, user: data.user }));
  }
};

const minePwd = async (req: any, res: any) => {
  const { user, pass, upPass } = req.body.data || req.body || {};
  const mine = await verifyAuthentication(req.headers.authorization || req.headers.token) as { err, data; }; // 验证token
  if (!mine.data) return res.send(formatReqLog(401, 'token已过期', null, { goal: req.originalUrl }));
  if (!upPass) return res.send(formatReqLog(400, '新密码格式有误', null, { goal: req.originalUrl, user }));
  const isPass = await selectQueryFunc(`SELECT * FROM users WHERE [user]='${mine.data.user}' AND pass='${pass}'`);
  if (isPass.data) {
    const upQuery = `UPDATE users SET pass='${upPass}' WHERE [user]='${user}' AND id=${mine.data.id}`;
    selectQueryFunc(upQuery, (insertErr, _) => {
      if (insertErr) {
        res.send(formatReqLog(300, '修改失败', insertErr, { goal: req.originalUrl, user, sql: upQuery }));
      } else {
        res.send(formatReqLog(200, '修改成功', user, { goal: req.originalUrl, user }));
      }
    });
  } else res.send(formatReqLog(300, '旧密码错误', null, { goal: req.originalUrl, user }));
};

const restPwd = async (req: any, res: any) => {
  const { user } = req.body.data || req.body || {};
  const mine = await verifyAuthentication(req.headers.authorization || req.headers.token) as { err, data; }; // 验证token
  if (!mine.data) return res.send(formatReqLog(401, 'token已过期', null, { goal: req.originalUrl }));
  if (user === 'sysadmin') return res.send(formatReqLog(403, '无权限修改', null, { goal: req.originalUrl }));
  const isUser = await selectQueryFunc(`SELECT * FROM users WHERE [user]='${user}'`);
  if (isUser.data) {
    if (mine.data.jurisdiction <= isUser.data.jurisdiction) {
      return res.send(formatReqLog(403, '无权限修改', null, { goal: req.originalUrl, user: mine.data.user }));
    }
    const upQuery = `UPDATE users SET pass='000000' WHERE [user]='${user}'`;
    selectQueryFunc(upQuery, (insertErr, _) => {
      if (insertErr) {
        res.send(formatReqLog(300, '重置失败', insertErr, { goal: req.originalUrl, user, sql: upQuery }));
      } else {
        res.send(formatReqLog(200, '重置成功', `${user}:000000`, { goal: req.originalUrl, user: mine.data.user }));
      }
    });
  } else res.send(formatReqLog(300, `暂无${user}用户`, null, { goal: req.originalUrl, user: mine.data.user }));
};

const add = async (req: any, res: any) => {
  const data = req.body.data || req.body || {};
  data.id = null;
  // 验证码注册
  // const cookie = getStringBetweenChars(req.headers.cookie || '', 'captchaCode=', ';');
  // const cookie = req.headers.cookie?.split('captchaCode=')[1];
  const regex = /captchaCode=([^;]+)/;
  const cookie = req.headers.cookie?.match(regex)[1];
  if (!cookie || data.captcha !== crypto.decrypt(cookie)) return res.send(formatReqLog(400, '验证码错误', null, { goal: req.originalUrl }));
  if (data.user) {
    const isUser = await selectQueryFunc(`SELECT * FROM users WHERE [user]='${data.user}'`);
    if (isUser.data) {
      res.send(formatReqLog(300, '该用户已存在！', null, { goal: req.originalUrl, user: data.user }));
    } else {
      const addQuery = `
        INSERT INTO users ([user], pass, createTime)
          VALUES (
            '${data.user}',
            '${crypto.decrypt(data.pass)}',
            '${dayjs().format('YYYY-MM-DD HH:mm:ss')}'
          )
      `;
      await selectQueryFunc(addQuery, (insertErr, _) => {
        if (insertErr) {
          res.send(formatReqLog(300, '添加失败', insertErr, { goal: req.originalUrl, user: data.user, sql: addQuery }));
        } else {
          res.send(formatReqLog(200, '添加成功', data.user, { goal: req.originalUrl, user: data.user }));
        }
      });
    }
  } else {
    res.send(formatReqLog(400, '传参有误', null, { goal: req.originalUrl, user: data.user }));
  }
};

const up = async (req: any, res: any) => {
  const data = req.body.data || req.body || {};
  if (data.user && data.id) {
    const isUser = await selectQueryFunc(`SELECT * FROM users WHERE id=${data.id}`);
    const mine = await verifyAuthentication(req.headers.authorization || req.headers.token) as { err, data; };
    if (!mine.data) return res.send(formatReqLog(401, 'token已过期', null, { goal: req.originalUrl }));
    if (data.user === mine.data.user) {
      const upQuery = `
        UPDATE users SET photo='${(data.photo || data.photo === '') ? data.photo : isUser.data.photo}',
          name='${(data.name || data.name === '') ? data.name : isUser.data.name}',
          sex=${(data.sex || data.sex === 0) ? data.sex : isUser.data.sex},
          age=${(data.age || data.age === 0) ? data.age : isUser.data.age},
          upTime='${dayjs().format('YYYY-MM-DD HH:mm:ss')}',
          phone='${(data.phone || data.phone === '') ? data.phone : isUser.data.phone}',
          email='${(data.email || data.email === '') ? data.email : isUser.data.email}'
          WHERE id=${data.id}
      `;
      selectQueryFunc(upQuery, (insertErr, _) => {
        if (insertErr) {
          res.send(formatReqLog(300, '修改失败', insertErr, { goal: req.originalUrl, user: data.user, sql: upQuery }));
        } else {
          res.send(formatReqLog(200, '修改成功', data.user, { goal: req.originalUrl, user: data.user }));
        }
      });
      return;
    }
    if (mine.data.jurisdiction < isUser.data.jurisdiction || mine.data.jurisdiction < 1 || data.user === 'sysadmin') {
      return res.send(formatReqLog(403, '无权限修改', null, { goal: req.originalUrl, user: data.user }));
    }
    if (isUser.data) {
      const upQuery = `
        UPDATE users SET jurisdiction=${(data.jurisdiction || data.jurisdiction === 0) ? data.jurisdiction : isUser.data.jurisdiction},
          photo='${data.photo || isUser.data.photo}', name='${data.name || isUser.data.name}',
          sex=${(data.sex || data.sex === 0) ? data.sex : isUser.data.sex},
          age=${(data.age || data.age === 0) ? data.age : isUser.data.age},
          upTime='${dayjs().format('YYYY-MM-DD HH:mm:ss')}' WHERE id=${data.id}
      `;
      selectQueryFunc(upQuery, (insertErr, _) => {
        if (insertErr) {
          res.send(formatReqLog(300, '修改失败', insertErr, { goal: req.originalUrl, user: mine.data.user, sql: upQuery }));
        } else {
          res.send(formatReqLog(200, '修改成功', data.user, { goal: req.originalUrl, user: mine.data.user }));
        }
      });
    } else {
      res.send(formatReqLog(300, '暂无用户', null, { goal: req.originalUrl, user: data.user }));
    }
  } else {
    res.send(formatReqLog(400, '传参有误', null, { goal: req.originalUrl, user: data.user }));
  }
};

const del = async (req: any, res: any) => {
  const data = req.body.data || req.body || [];
  const mine = await verifyAuthentication(req.headers.authorization || req.headers.token) as { err, data; };
  if (!mine.data) return res.send(formatReqLog(401, 'token已过期', null, { goal: req.originalUrl }));
  if (mine.data.jurisdiction < 1) {
    return res.send(formatReqLog(403, '无权限修改', null, { goal: req.originalUrl, user: mine.data.user }));
  }
  let i = 0;
  let timer = 0 as any;
  let tip = '';
  timer = setInterval(async () => {
    const isUser = await selectQueryFunc(`SELECT * FROM users WHERE id=${data[i]}`);
    console.log(isUser, data[i], i, mine);
    if (isUser.data.user === 'sysadmin' || isUser.data.user === mine.data.user || mine.data.jurisdiction <= isUser.data.jurisdiction) {
      tip += `${isUser.data.user}-无权限修改,`;
    } else {
      await selectQueryFunc(`DELETE users WHERE id=${data[i]}`);
      tip += `${isUser.data.user}-已删除,`;
    }
    i++;
    if (i === data.length) {
      clearInterval(timer);
      res.send(formatReqLog(200, '删除成功', tip, { goal: req.originalUrl, user: mine.data.user }));
    }
  }, 800);
};

const find = async (req: any, res: any) => {
  const page = req.query.page || 1;
  const limit = req.query.limit || 10;
  const name = String(req.query.name || '') || '';
  const field = req.query.field || '';
  const order = (req.query.order) ? 'ASC' : 'DESC'; // false倒序
  const mine = await verifyAuthentication(req.headers.authorization || req.headers.token) as { err, data; };
  if (!mine.data) return res.send(formatReqLog(401, 'token已过期', null, { goal: req.originalUrl }));
  const selectQuery = `
    SELECT TOP ${limit} *
      FROM (
          SELECT *, ROW_NUMBER() OVER (ORDER BY ${field || 'id'} ${order}) AS rownum
          FROM users
          ${(field && !!name)
      ? `WHERE [${field}] LIKE '%${name}%'
                ${mine.data.jurisdiction < 1 ? `AND [user]='${mine.data.user}'` : ''}`
      : `${mine.data.jurisdiction < 1 ? `WHERE [user]='${mine.data.user}'` : ''}`}
      ) AS numbered
      WHERE rownum > ${(page - 1) * limit};
  `;
  await selectQueryFunc(selectQuery, async (insertErr, insertResults) => {
    if (insertErr) {
      res.send(formatReqLog(300, '查询失败', insertErr, { goal: req.originalUrl, user: mine.data.user, sql: selectQuery }));
    } else {
      insertResults.recordset.forEach(result => {
        result.pass = null;
      });
      const { data } = await selectQueryFunc(`
        SELECT COUNT(*) AS total_count FROM users ${(field && !!name)
          ? `WHERE [${field}] LIKE '%${name}%' ${mine.data.jurisdiction < 1 ? `AND [user]='${mine.data.user}'` : ''}`
          : `${mine.data.jurisdiction < 1 ? `WHERE [user]='${mine.data.user}'` : ''}`
        }
      `);
      res.send(formatReqLog(200, '查询成功', insertResults.recordset, {
        goal: req.originalUrl, user: mine.data.user, total: data.total_count
      }));
    }
  });
};

const info = async (req: any, res: any) => {
  const key = req.params.id;
  const mine = await verifyAuthentication(req.headers.authorization || req.headers.token) as { err, data; };
  if (!mine.data) return res.send(formatReqLog(401, 'token已过期', null, { goal: req.originalUrl }));
  await selectQueryFunc("SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'users'", async (insertErr, insertResults) => {
    if (!insertErr) {
      const columns = insertResults.recordset.filter(field => field.COLUMN_NAME !== 'pass').map(item => `[${item.COLUMN_NAME}]`).join(',');
      const query = `SELECT ${columns} FROM users WHERE id=${key}`;
      await selectQueryFunc(query, (insertErr, { recordset }) => {
        if (insertErr) {
          res.send(formatReqLog(300, '查询失败', insertErr, { goal: req.originalUrl, user: mine.data.user, sql: query }));
        } else {
          res.send(formatReqLog(200, '查询成功', recordset[0], { goal: req.originalUrl, user: mine.data.user }));
        }
      });
    }
  });
};

const derive = async (req: any, res: any) => {
  const page = req.query.page || 1;
  const limit = req.query.limit || 10;
  const name = String(req.query.name || '') || '';
  const field = req.query.field || '';
  const order = (req.query.order) ? 'ASC' : 'DESC'; // false倒序
  const mine = await verifyAuthentication(req.headers.authorization || req.headers.token) as { err, data; };
  if (!mine.data) return res.send(formatReqLog(401, 'token已过期', null, { goal: req.originalUrl }));
  const selectQuery = `
    SELECT TOP ${limit} *
      FROM (
          SELECT *, ROW_NUMBER() OVER (ORDER BY ${field || 'id'} ${order}) AS rownum
          FROM users
          ${(field && !!name) ? `WHERE [${field}] LIKE '%${name}%'` : ''}
      ) AS numbered
      WHERE rownum > ${(page - 1) * limit};
  `;
  await selectQueryFunc(selectQuery, async (insertErr, { recordset }) => {
    if (insertErr) {
      res.send(formatReqLog(300, '查询失败', insertErr, { goal: req.originalUrl, user: mine.data.user, sql: selectQuery }));
    } else {
      const jsonArray = [[
        ['标识'], ['权限'], ['名称'], ['账号'], ['性别'], ['年龄'], ['头像'], ['邮箱'], ['联系'], ['创建时间'], ['修改时间']
      ]];
      try {
        const data = (recordset && recordset.length > 0) ? recordset : [recordset];
        for (let i = 0; i < data.length; i++) {
          if (data[i]) {
            jsonArray.push([
              [data[i].id], [data[i].jurisdiction], [data[i].name], [data[i].user],
              [data[i].sex], [data[i].age], [data[i].photo], [data[i].email],
              [data[i].phone], [data[i].createDate], [data[i].upDate]
            ]);
          }
        }
        const xls = nodexlsx.build(
          [{ name: 'sheet1', data: jsonArray, options: {} }]
        );
        const time = new Date().getTime();
        const filePath = path.join(__dirname, '../static/derive/');
        await mkdirs(filePath);
        fs.writeFileSync(filePath + `${time}.xlsx`, xls, 'binary');
        res.send(formatReqLog(200, '下载链接', `api/file/download/derive/${time}.xlsx`, { goal: req.originalUrl, user: mine.data.user }));
      } catch (err) {
        res.send(formatReqLog(300, '导出失败', null, { goal: req.originalUrl, user: mine.data.user }));
      }
    }
  });
};

export = {
  login,
  up,
  add,
  del,
  find,
  info,
  minePwd,
  restPwd,
  derive
};
